SQL SERVER 分区表的总结

SQL Server 2005 分区表实践——建立分区表（partition table）

SQL Server中数据库文件的存放方式，文件和文件组 (fromCareySon)

T-SQL查询进阶--理解SQL SERVER中的分区表 (from CareySon)

一直对于表分区和filegroup的概念不是很清晰，今天通过具体的实例来学习什么事filegroup和partition，以及他们的作用。

对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表,索引和大对象（LOB）

使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。也就是可以指定将表和索引存储在不同的文件上面。

使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能.

SQL SERVER会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件A设置的大小为文件B的两倍，新增一个数据占用三页(Page),则按比例将2页分配到文件A中，1页分配到文件B中.

    在SQL SERVER 2008之后，还新增了文件流数据文件和全文索引文件.

    我们可以通过sys.database_files这个视图查看数据库中的文件情况:

可以通过TSQL语句来创建文件组，也可以通过SSMS来创建文件组，这个在后面会提到。这里不再重复。下面我们重点来介绍如何将索引创建在指定的filegroup中，而不跟数据放在一起。首先来看我创建好的filegroup，已经这些filegroup所对应的files，如下图所示：

然后我们通过如下TSQL语句来测试

总结：

使用多个文件分布数据到多个硬盘中可以极大的提高IO性能.放在一个磁盘中基本没有效果。

应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。

在现实环境里，可以试想下面的情形。一个股票交易系统，每一笔交易都会有一个流水号，是递增且不可重复的。而客户发过来的交易请求，都要存储在同一张交易表里。每一个新的交易，都要插入一条新记录。如果设计者选择在流水号上建聚集索引（这也是很自然的），就容易遇到Hot Page的PAGELATCH资源瓶颈。在同一时间，只能有一个用户插入一笔交易。

怎样才能解决或者缓解这种瓶颈呢？

还是以上面那个股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票的行业属性，将一张交易表分成若干个分区。在SQL Server里，已分区表（Partitioned Table）的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以同一个时间发生的交易记录，因其行业不同，也会被分别保存在不同的分区里。这样，在同一个时间点，可以插入不同行业的交易记录。每个分区上的Hot Page（接受新数据插入的page）就不那么hot了。

在我的事例中，是有一张SalesOrderDetail表，其数据量很大，我希望按照UnitPrice这个字段进行分区。下面来看具体步骤。

在sql server中好像没有create filegroup的说法，只是在现成的数据库中添加filegroup而已。下面的代码中首先创建数据库，然后添加四个filegroup，tsql代码如下所示：

执行完以后我们可以在TEST数据库的properties中看到我们添加的四个filegroup，如下图所示：

在创建完filegroup以后，我们为每一个filegroup创建一个次要数据文件，因为每一个数据库只能有一个primary datafile，也就是mdf文件，但是可以有多个次要数据文件，也就是.ndf文件。为filegroup创建ndf数据文件的TSQL语句如下图所示:

执行完上述语句以后，我们可以查看TEST数据库的file properties，如下图所示：

我们可以看到四个文件的大小都是2MB。

在当前数据库中创建一个函数，该函数可根据指定列的值将表或索引的各行映射到分区。 使用 CREATE PARTITION FUNCTION 是创建已分区表或索引的第一步。 在 SQL Server 2012 中，一张表或一个索引最多可以有 15,000 个分区。

创建分区函数的具体如法如下：

在本事例中，需要有5个分区，本实例创建的分区函数如下所示：

其中RANGE LEFT|RIGHT表示当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧，就是等于号在哪一边）。 如果未指定，则默认值为 LEFT。比如在我的分区函数中指定的间隔是(500,1000,1500,2000)，并且是RANGE RIGHT，那么我的范围就是

创建分区架构的TSQL如下所示：

从上述TSQL中我们可以发现，在创建分区架构的时候关联了分区函数以及具体的5个filegroup。

注意：这里并不一定必须要求有5个filegroup，我们可以填写相同的filegroup，但是需要填写5次filegroup，因为有5个分区。

需要注意的是分区列UnitPrice必须有唯一约束或者是聚集索引。所以在这里我创建聚集索引的时候将UnitPrice列也添加进去了。如果不讲UnitPrice设为聚集索引，也就是让此列唯一，那么在执行上述命令的时候会报如下错误：

Msg 1908, Level 16, State 1, Line 2 Column 'UnitPrice' is partitioning column of the index 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'. Partition columns for a unique index must be a subset of the index key. Msg 1750, Level 16, State 0, Line 2 Could not create constraint. See previous errors.

在执行完上面的操作以后我们再去看看ndf文件有没有变化，如下图所示，我们发现ndf文件大小有增长，这表明已经往这几个分区中写入了数据。

上边的表结构是通过select * into语句来创建表的，这种方式没有普遍性，下面我们通过create table来创建表结构：

在上面语句中，我们发现：

在创建好表结构以后，我们往里面插入数据。如果一条一条插入数据比较慢的话，我们可以在AdventureWorks2008R2.Sales.SalesOrderDetail表中导入，导入语句如下：

6.查看分区表各分区数据情况（数据行数，最大最小 UnitPrice值）

执行如下查询命令

其中fn_Partition_SalesOrderDetail_UnitPrice(UnitPrice)是分区函数，UnitPrice是列名。

使用第一种方法，也就是select * into的方法导入数据，其查询结果为：

我们可以看到每一个分区上面都有数据。有些总数据量小于2MB，所以ndf文件大小没有改变。如果ndf文件文件大小变化不大，我们可以多执行几次上面的数据导入语句。

使用第二种方法插入数据，一共执行了三次，其最后文件大小如下图所示：

分区上存储的数据统计信息如下：

从上述查询结果我们可以发现在partition4(对应FG_TEST_SalesOrderDetail_UnitPrice_3_data_1.ndf这个次要数据文件)中，数据行只有2817条，这也说明了为什么在上图中只有FG_TEST_SalesOrderDetail_UnitPrice_3_data_1.ndf这个文件大小没有增长，依然是2048KB。